package com.hymjweb.demo.framework.util.util;



import com.hymjweb.demo.framework.util.DataTypeUtil;
import com.hymjweb.demo.framework.util.DateUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class PoiUtil {

	/**
	 * 根据文件获取WorkBook对象，支持Exel2003和Excel2010
	 * @param f
	 * @param fi
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkBook(File f, FileInputStream fi) throws Exception {
		String fn = f.getAbsolutePath();
		Workbook wb = null;
		try {
			if (fn.endsWith("xlsx")) {
				wb = new XSSFWorkbook(fi);
			} else if (fn.endsWith("xls")) {
				wb = new HSSFWorkbook(fi);
			} else {
				throw new Exception("不是有效的Excel文件!");
			}
		} catch (Exception e) {
			throw new Exception("不是有效的Excel文件!");
		}

		return wb;
	}

	/**
	 * 获取Excel一行数据
	 * @param wb
	 * @param row
	 * @param ColumnCount	列数
	 * @return
	 */
	private static Object[] getExcelRowValue(Workbook wb, Row row, int ColumnCount) throws Exception {
		if (wb == null || row == null)
			return null;
		// int COL_COUNT = row.getLastCellNum();
		int COL_COUNT = ColumnCount;

		if (COL_COUNT <= 0)
			return null;
		Object[] arr = new Object[COL_COUNT];
		for (int i = 0; i < COL_COUNT; i++) {
			arr[i] = PoiUtil.getExcelCellValue(wb, row, i);
		}
		return arr;
	}

	public static Object[][] getExcelRowsValue(Workbook wb, Sheet sheet) throws Exception {
		if (wb == null || sheet == null)
			return null;
		int READ_ROW_COUNT = sheet.getLastRowNum();
		List<Object[]> list = new LinkedList<Object[]>();// 用于保存所有数据
		final int CellCount = getCellNumberOfSheet(sheet);
		for (int i = 0; i <= READ_ROW_COUNT; i++) {
			Row row = sheet.getRow(i);
			Object[] arr = getExcelRowValue(wb, row, CellCount);
			list.add(arr);
		}
		return list.toArray(new Object[list.size()][0]);
	}

	public static Object[][] getExcelRowsValue(Workbook wb, int sheetIndex) throws Exception {
		return getExcelRowsValue(wb, wb.getSheetAt(sheetIndex));
	}

	public static String getExcelCellValue(Workbook wb, Row row, int columnIndex) throws Exception {
		return PoiUtil.getExcelCellValue(wb, row, columnIndex, -1, false);
	}

	public static String getExcelCellValue(Workbook wb, Row row, int columnIndex, boolean refine) throws Exception {
		return PoiUtil.getExcelCellValue(wb, row, columnIndex, -1, refine);
	}

	/**
	 * 
	 * @param row
	 * @param columnIndex 列索引，从0开始
	 * @param cellType
	 * @param refine
	 *            去掉整数后面的小数点,如月份201103月时,取值会出现201103.0,需要去掉后面的.0
	 * @return
	 */
	private static String getExcelCellValue(Workbook wb, Row row, int columnIndex, int cellType, boolean refine) throws Exception {
		try {
			if (row == null)
				return "";
			Cell cell = row.getCell(columnIndex);
			if (cell == null)
				return "";
			String retv = "";
			if (cellType == -1)
				cellType = cell.getCellType();
			// CELL_TYPE_NUMERIC 0 数字
			// CELL_TYPE_STRING 1 字符串
			// CELL_TYPE_FORMULA 2 公式
			// CELL_TYPE_BLANK 3 空格
			// CELL_TYPE_BOOLEAN 4 布尔值
			// CELL_TYPE_ERROR 5 错误
			switch (cellType) {
			// 单元格类型为数字
			case Cell.CELL_TYPE_NUMERIC:
				if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
					Date d = cell.getDateCellValue();
					if (d != null)
						retv = DateUtil.changeStrDate(d, "yyyy-MM-dd");
				} else {
					// 取数字单元格的值
					double d = cell.getNumericCellValue();
					retv = String.valueOf(d);
				}
				break;
			// 单元格类型为字符串
			case Cell.CELL_TYPE_STRING:
				try {
					retv = cell.getStringCellValue();
				} catch (Exception e) {
					String emsg = e.getMessage() + "";
					if (emsg.indexOf("Cannot get a text value from a numeric cell") >= 0) {
						double d = cell.getNumericCellValue();
						retv = String.valueOf(d);
					}
				}
				break;
			// 单元格类型为公式
			case Cell.CELL_TYPE_FORMULA:
				// 不读取公式
				// String formula = cell.getCellFormula();
				// content = content + formula+" ";
				FormulaEvaluator evaluator = null;
				if (wb instanceof HSSFWorkbook) {
					evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
				} else if (wb instanceof XSSFWorkbook) {
					evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
				}
				double d = evaluator.evaluate(cell).getNumberValue();
				retv = new BigDecimal(d).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
				break;
			// 单元格类型为空白
			case Cell.CELL_TYPE_BLANK:
				break;
			// 单元格类型为布尔值
			case Cell.CELL_TYPE_BOOLEAN:
				// boolean bool = cell.getBooleanCellValue();
				// content = content + bool+" ";
				break;
			// 单元格类型为错误
			case Cell.CELL_TYPE_ERROR:
				// byte errorCode = cell.getErrorCellValue();
				// content = content + errorCode+" ";
				break;
			default:
				break;
			}
			retv = DataTypeUtil.toNotNullString(retv).trim();
			final String zero = ".0";
			if (refine && retv.endsWith(zero)) {
				int pos = retv.lastIndexOf(zero);
				if (pos > 0) {
					retv = retv.substring(0, pos);
				}
			}
			return DataTypeUtil.toNotNullString(retv).trim();
		} catch (Exception e) {
			throw new Exception(
					"[" + row.getSheet().getSheetName() + "]第" + (row.getRowNum() + 1) + "行" + e.getMessage());
		}
	}

	private static void setExcelValue(Cell cell, int CellType, Object v) throws Exception {
		PoiUtil.setExcelValue(cell, CellType, v, true);
	}

	private static void setExcelValue(Cell cell, int CellType, Object v, boolean IgnoreIfEmpty) throws Exception {
		if (v == null && IgnoreIfEmpty) {
			return;
		}
		if (v == null) {
			return;
		}
		switch (CellType) {
		case Cell.CELL_TYPE_NUMERIC:
			if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
				if (v instanceof Date) {
					cell.setCellValue((Date) v);
				} else {
					cell.setCellValue(com.hymjweb.demo.framework.util.DateUtil.strToDate(DataTypeUtil.toNotNullString(v)));
				}
			} else {
				cell.setCellValue(DataTypeUtil.toDouble(v, true));
			}
			break;
		case Cell.CELL_TYPE_STRING:
			cell.setCellValue(DataTypeUtil.toNotNullString(v));
			break;
		case Cell.CELL_TYPE_FORMULA:
			try {
				cell.setCellFormula(DataTypeUtil.toNotNullString(v));
			} catch (Exception e) {
			}
			break;
		case Cell.CELL_TYPE_BLANK:
			cell.setCellValue(DataTypeUtil.toNotNullString(v));
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			break;
		case Cell.CELL_TYPE_ERROR:
			break;
		default:
			break;
		}
	}

	public static void setExcelValue(Cell cell, Object v) throws Exception {
		if (v == null)
			return;
		if (v instanceof String || v instanceof StringBuffer) {
			setExcelValue(cell, Cell.CELL_TYPE_STRING, DataTypeUtil.toNotNullString(v));
		} else if (v instanceof Date) {
			setExcelValue(cell, Cell.CELL_TYPE_NUMERIC, v);
		} else if (v instanceof Long || v instanceof Double || v instanceof Integer || v instanceof BigInteger
				|| v instanceof BigDecimal) {
			setExcelValue(cell, Cell.CELL_TYPE_NUMERIC, v);
		} else {
		    setExcelValue(cell, Cell.CELL_TYPE_STRING, DataTypeUtil.toNotNullString(v));
		}
	}

	/**
	 * 获取一个表格的最大列数
	 * @param sheet
	 * @return
	 */
	private static int getCellNumberOfSheet(Sheet sheet) {
		int rowMax = sheet.getLastRowNum();
		int CellNumber = 0;
		for (int i = 0; i <= rowMax; i++) {
			try {
				int tempCellNumber = sheet.getRow(i).getLastCellNum();
				if (tempCellNumber > CellNumber)
					CellNumber = tempCellNumber;
			} catch (Exception e) {
				System.out.println(sheet.getRow(i));
				System.out.println(sheet.getPhysicalNumberOfRows());
			}

		}
		return CellNumber;
	}

	public static void saveWorkBook(Workbook wb, String fullFileName) throws Exception {
		FileOutputStream fileOut = new FileOutputStream(fullFileName);
		wb.write(fileOut);
		fileOut.close();
	}

//	public static File createExcel(String fileName, String sheetName, String[] titles, Object[][] datas)
//			throws Exception {
//		if (fileName == null || sheetName == null || titles == null || titles.length < 1 || datas == null
//				|| datas.length < 1)
//			return null;
//		HSSFWorkbook workbook = createWorkBook(sheetName, titles, datas);
//		// 生成文件
//		fileName = NutzUtil.getConfig().get("tempFilePath") + fileName;
//		File f = new File(fileName);
//		if (f.exists())
//			f.delete();
//		FileOutputStream fOut = new FileOutputStream(f);
//		workbook.write(fOut);
//		fOut.flush();
//		fOut.close();
//		return f;
//	}

	/**根据二维数组及sheetName创建WorkBook对象
	 * 
	 * @param sheetName
	 * @param titles
	 * @param datas
	 * @return
	 * @throws Exception 
	 * @author 余伟
	 * @serialData 2018年4月12日
	 */
	public static HSSFWorkbook createWorkBook(String sheetName, String[] titles, Object[][] datas) throws Exception {
		if (sheetName == null || titles == null || titles.length < 1 || datas == null || datas.length < 1)
			return null;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = null;
		final int EXCEL_MAX_LEN = 65534;
		int bs = datas.length / EXCEL_MAX_LEN;// 倍数
		int ys = datas.length % EXCEL_MAX_LEN;// 余数
		int colnum = datas[0].length;
		for (int kk = 0; kk <= bs; kk++) {
			int start = 0;
			int end = 0;
			int excel_row_start = 0;
			if (bs == 0) {
				start = 0;
				end = ys - 1;
			} else if (kk == 0) {
				start = 0;
				end = EXCEL_MAX_LEN;
			} else if (kk == bs) {
				start = kk * EXCEL_MAX_LEN + 1;
				end = kk * EXCEL_MAX_LEN + ys - 1;
			} else {
				start = kk * EXCEL_MAX_LEN + 1;
				end = (kk + 1) * EXCEL_MAX_LEN;
			}
			// System.out.println(start + ":" + end);
			sheet = workbook.createSheet(sheetName + "_" + kk);
			HSSFRow row = sheet.createRow((short) 0);
			HSSFCellStyle style = createTableTitleStyle(workbook);

			// 创建标题
			for (int i = 0; i < titles.length; i++) {
				String title = DataTypeUtil.toNotNullString(titles[i]);
				title = title.replaceAll(" ", "");
				title = title.replaceAll("　", "");
				title = title.replaceAll("\\*", "");
				title = replaceHtml(title);
				HSSFCell cell = row.createCell(i);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(title);
				cell.setCellStyle(style);
			}

			// 创建内容
			for (int i = start; i <= end; i++) {
				excel_row_start++;
				row = sheet.createRow(excel_row_start);
				for (int j = 0; j < colnum; j++) {
					HSSFCell cell = row.createCell(j);

					Object v = datas[i][j];
					if (v != null) {
						setAlignByValue(workbook, cell, v);
						//						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						//						cell.setCellValue(v);
						setExcelValue(cell, v);
					}
				}
			}
			for (int i = 0; i < titles.length; i++) {
				sheet.autoSizeColumn((short) i);
			}
		}
		return workbook;
	}


	private static void setAlignByValue(HSSFWorkbook workbook, HSSFCell cell, Object v) {
		if (v == null) {
			setAlign(workbook, cell, HSSFCellStyle.ALIGN_CENTER);
			return;
		}
		if (v instanceof String || v instanceof StringBuffer) {
			setAlign(workbook, cell, HSSFCellStyle.ALIGN_CENTER);
		} else if (v instanceof Date) {
			setAlign(workbook, cell, HSSFCellStyle.ALIGN_CENTER);
		} else if (v instanceof Long || v instanceof Integer || v instanceof BigInteger) {
			setAlign(workbook, cell, HSSFCellStyle.ALIGN_CENTER);
		} else if (v instanceof BigDecimal || v instanceof Double || v instanceof Float) {
			HSSFCellStyle cellStyle = setAlign(workbook, cell, HSSFCellStyle.ALIGN_RIGHT);
			cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		}
	}

	/**
	 * 创建Excel内容大标题样式
	 * 
	 * @param wb
	 * @return
	 */
	private static HSSFCellStyle createTableTitleStyle(HSSFWorkbook wb) {
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setFontName("宋体");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setItalic(false);
		font.setStrikeout(false);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setFont(font);
		return style;
	}

	private static HSSFCellStyle setAlign(HSSFWorkbook workbook, HSSFCell cell, short ALign) {
		HSSFCellStyle style1 = cell.getCellStyle();
		if (style1 == null)
			style1 = workbook.createCellStyle();
		style1.setAlignment(ALign);
		style1.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		cell.setCellStyle(style1);
		return style1;
	}

	public static String replaceHtml(String s) {
		if (DataTypeUtil.isEmptyStr(s))
			return "";
		s = s.replaceAll("<.+?>", "");
		s = s.replaceAll("&nbsp;", "");
		s = s.replaceAll("&gt;", "");
		s = s.replaceAll("'", "''");
		s = s.replaceAll("\n\n", "");
		return s;
	}

    /**
     * @author zhang yong
     * @serialData 20181214
     * 给sheet表格增加《处理结果》栏具体信息
     * @param sheetRow 表格行对象
     * @param sb 提示信息对象
     */
    public static void renderResultColumn(Row sheetRow, StringBuilder sb, int width) throws Exception {
        if (sb.length() == 0) {
			sb.append("其他");
		}
        String result = sb.toString();
        boolean isSuccess = "处理成功".equals(result);
        addCell(sheetRow,isSuccess ? result : "处理失败",width);
        addCell(sheetRow,isSuccess ? "" : result, width+1);
    }

    /**
     * @author zhang yong
     * @serialData 20181214
     * 给sheet表格增加《处理结果》一栏
     * @param sheet 表格对象
     */
    public static void renderSheet(Sheet sheet, int width, int maxLength) throws Exception {
        Row row = sheet.getRow(2);
        renderTitleRow(sheet,row,width,maxLength);
    }

    /**
     * @author zhang yong
     * @serialData 20181214
     * 给sheet表格增加《处理结果》一栏
     * @param sheet 表格对象
     * @param row 表格行对象
     */
    public static void renderTitleRow(Sheet sheet, Row row, int width, int maxLength) throws Exception {
        renderTitleRow(sheet,row,"处理结果（系统反馈）",width,0);
        renderTitleRow(sheet,row,"错误信息（系统反馈）",width+1,maxLength);
    }

    /**
     * 给sheet表格标题栏增加一栏
     * @author zhang yong
     * @serialData 20190328
     * @param sheet 表格对象
     * @param row 表格行对象
     * @param value 单元格中的值
     * @param index 表格列索引
     * @param width 列宽度
     */
    public static void renderTitleRow(Sheet sheet, Row row, String value, int index, int width) throws Exception {
        addCell(row,value,index);
        sheet.setColumnWidth(index, (20 + width) * 256);
    }

    /**
     * 给sheet行的一个单元格设置值
     * @author zhang yong
     * @serialData 20190328
     * @param row sheet行
     * @param value 单元格值
     * @param index 单元格索引
     */
    public static void addCell(Row row, String value, int index) throws Exception {
        DataTypeUtil.isEmptyStrThenThrow(row,"sheet行对象为空，请检查excel表格数据");
        Cell cell = row.createCell(index);
        cell.setCellValue(value);
        Cell preCell = row.getCell(index - 1);
        if (null != preCell) {
            cell.setCellStyle(preCell.getCellStyle());
        }
    }

	public static File createExcel(String fileName, String sheetName, String[] titles, Object[][] datas)
			throws Exception {
		if (fileName == null || sheetName == null || titles == null || titles.length < 1 || datas == null
				|| datas.length < 1){
			return null;
		}

		HSSFWorkbook workbook = createWorkBook(sheetName, titles, datas);
		File f = new File(fileName);
		if (f.exists()){
			f.delete();
		}
		FileOutputStream fOut = new FileOutputStream(f);
		workbook.write(fOut);
		fOut.flush();
		fOut.close();
		return f;
	}
}
